package server;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.sql.SQLException;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.ResultSet;

public class WordDB extends DB {

    public WordDB(final String DBName, final String tableName, final String varStr) {
        super(DBName, tableName, varStr);
    }

    public boolean updateDictionaryByFile(File file) {
        final ArrayList<Word> list=new ArrayList<Word>();
        try{
            final FileInputStream fis = new FileInputStream(file);
            final BufferedReader br = new BufferedReader(new InputStreamReader(fis));
            String str = null;
            int n = 0;
            while((str = br.readLine()) != null){
                String word = str;
                while(!(str = br.readLine()).equals("-")) word = word +"\n"+str;
                String phone = br.readLine();
                while(!(str = br.readLine()).equals("--")) phone = phone +"\n"+str;
                String synos = br.readLine();
                while(!(str = br.readLine()).equals("---")) synos = synos +"\n"+str;
                if(str.equals("---")) n++;
                list.add(new Word(word, phone, synos));
            }
            br.close();
            addBatchData(list);
            System.out.println(n);
            return true;
        }catch(final IOException e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean addBatchData(final java.util.ArrayList<Word> list) {
        if (currentTable == null)
            return false;
        try {
            final String sql = "insert into " + currentTable + " (word,phone,synos) values(?,?,?)";
            final java.sql.PreparedStatement statement = con.prepareStatement(sql);
            for (final Word word : list) {
                statement.setString(1, word.getWord());
                statement.setString(2, word.getPhone());
                statement.setString(3, word.getSynos());
                statement.addBatch();
                statement.clearParameters();
            }
            statement.executeBatch();
            statement.clearBatch();
            statement.close();
            return true;
        } catch (final SQLException e) {
            return false;
        }
    }

    public boolean add(final Object word) {
        if (currentTable == null)
            return false;
        try {
            final Word ptr = (Word) word;
            final String sql = "insert into " + currentTable + " (word,phone,synos) values(" + "\'" + ptr.getWord()
                    + "\'" + ",\'" + ptr.getPhone() + "\'" + ",\'" + ptr.getSynos() + "\'" + ")";
            final Statement st = con.createStatement();
            boolean ret = st.execute(sql);
            st.close();
            return ret;
        } catch (final SQLException e) {
            return false;
        }
    }

    public boolean updateDictionaryByWords(final LinkedHashMap<String,Word> words) {
        Iterator<Map.Entry<String,Word>> iterator= words.entrySet().iterator();
        boolean ret = true;
        while(iterator.hasNext()){
            Map.Entry<String,Word> entry = iterator.next();
            ret = ret & update(entry.getKey(), entry.getValue());
        }
        return ret;
    }

    public boolean update(final Object before, final Object after) {
        if (currentTable == null)
            return false;
        try {
            final String word, phone, synos;
            word = (String) before;
            phone = ((Word) after).getPhone();
            synos = ((Word) after).getSynos();
            final String sql = "update " + currentTable + " set phone=\"" + phone + "\",synos=\"" + synos
                    + "\" where word=\"" + word + "\"";
            final Statement st = con.createStatement();
            st.executeUpdate(sql);
            st.close();
            return true;
        } catch (final SQLException e) {
            return false;
        }
    }

    public boolean delete(final Object word) {
        if (currentTable == null)
            return true;
        try {
            final String sql = "delete from " + currentTable + " where word=\'" + ((String) word) + "\'";
            final Statement st = con.createStatement();
            st.executeUpdate(sql);
            st.close();
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    public Word findWord(final String wordspell) {
        return (Word) query(wordspell);
    }

    public Object query(final Object word) {
        String [] tables;
        try {
        	 String sql ="select table_name from information_schema.tables where table_schema='worddb';";
        	 Statement st = con.createStatement();
            final ResultSet rSet = st.executeQuery(sql);
            Word ret = null;
            while (rSet.next()) {
            	System.out.println(rSet.getString(1));
				sql="select * from " + rSet.getString(1) + " where word=\"" + ((String) word) + "\"";
	            st = con.createStatement(); 
	            ResultSet rSet1 = st.executeQuery(sql);
	            if(rSet1.next()) {
	            	ret = new Word(rSet1.getString(1), rSet1.getString(2), rSet1.getString(3));
	            	break;
	            }
            }
            return ret;
        }catch (SQLException e) {
        	return null;
        }
    }

    public LinkedHashMap<String,Word> getStudyWords(final String dictionaryname,final int from,final int to) {
        setTable(dictionaryname);
        return queryBatch(from, to);
    }

    public LinkedHashMap<String,Word> getReviewWords(final String dictionaryname,final int end,final int amount) {
        setTable(dictionaryname);
        LinkedHashMap<String, Word> ret = new LinkedHashMap<>();
        LinkedHashMap<String, Word> all = queryBatch(0, end);
        Set<Word> s=new HashSet<>();
        List<Word> list = new ArrayList<>(all.values());
        int n=0,index=0;
        do {
            int pos = (int) Math.floor(Math.random() * list.size());
            Word word = list.get(pos);
            if(!s.contains(word)){
                s.add(word);
                ret.put(word.getWord(), word);
                n++;
            }
            index++;
        }while(n<amount);
        //||2*index>amount
        return ret;
    }

    public LinkedHashMap<String, Word> queryBatch(final int from, final int to) {
        if (currentTable == null)
            return null;
        try {
            String sql = "select * from " + currentTable + " limit ";
            sql = sql + String.valueOf(from) + "," + String.valueOf(to - from + 1);
            final Statement st = con.createStatement();
            final ResultSet rSet = st.executeQuery(sql);
            LinkedHashMap<String, Word> ret = new LinkedHashMap<String, Word>(to - from + 1);
            while (rSet.next()) {
                final Word temp = new Word(rSet.getString(1), rSet.getString(2), rSet.getString(3));
                ret.put(temp.getWord(), temp);
            }
            rSet.close();
            st.close();
            return ret;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public LinkedHashMap<String, Word> queryTrans(final String trans) {
        if (currentTable == null)
            return null;
        try {
            final String sql = "select * from " + currentTable + " where synos like " + "\'%" + trans + "%\'";
            final Statement st = con.createStatement();
            final ResultSet rSet = st.executeQuery(sql);
            final LinkedHashMap<String, Word> ret = new LinkedHashMap<String, Word>();
            while (rSet.next()) {
                final Word temp = new Word(rSet.getString(1), rSet.getString(2), rSet.getString(3));
                ret.put(temp.getWord(), temp);
            }
            rSet.close();
            st.close();
            return ret;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public int getCountOfDictionary(final String dictionaryname){
        return queryNum(dictionaryname);
    }

    public int queryNum(String table) {
        if (table == null)
            table = currentTable;
        int num = 0;
        try {
            final String sql = "select count(*) from " + table;
            final Statement st = con.createStatement();
            final ResultSet rSet = st.executeQuery(sql);
            if (rSet.next())
                num = rSet.getInt(1);
        } catch (SQLException e) {
            return -1;
        }
        return num;
    }

    public void setTable(final String table) {
        currentTable = table;
        try {
            createTab(table, varStr);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public String querytable() {
        return currentTable;
    }
}